﻿/******************************************************/
// DALUser データアクセスクラス  
/******************************************************/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MAA.Entity;

namespace MAA.Data
{
    /// <summary>
    ///  ユーザーを参照のデータベースへのプロセスデータアクセス 
    /// </summary>
    public class DALUser
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// 電子メールに基づいて、ユーザーの詳細情報を取得します 
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public DataTable getAccInfo(string email)
        {
            DataTable tblUserInfo = new DataTable();

            // String query to get all user infomation by email
            string sql = @"SELECT   [User].ID,
                                    [User].Name,
                                    [User].[Password],
                                    [User].ProfilePicture,
                                    [User].Email,
                                    [User].Phone,
                                    [User].[Address],
                                    [User].ProvinceID,
                                    [User].RegisterDate,
                                    [User].ViolationNumber,
                                    [User].Status
                            FROM [User]
                            WHERE [User].Email = @email";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblUserInfo);
            conn.Close();

            return tblUserInfo;
        }

        /// <summary>
        /// ユーザーの詳細情報を取得します 
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getAccInfo(long userID)
        {
            DataTable userDataTable = new DataTable();
            SqlDataAdapter userAdapter = new SqlDataAdapter();
            userAdapter.SelectCommand = new SqlCommand();

            userAdapter.SelectCommand.CommandText = @"
                SELECT		[User].Name,
                            [User].[Password],
			                [User].ProfilePicture,
			                [User].Email,
			                [User].Phone,
			                [User].[Address],
                            [User].ProvinceID,
			                Province.Name AS Province,
                            [User].RegisterDate,
                            [User].ViolationNumber,
                            [User].Status
                FROM		[User], Province
                WHERE		Province.ID = [User].ProvinceID
	                AND		[User].ID = @userID";

            userAdapter.SelectCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            userAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            userAdapter.Fill(userDataTable);
            userAdapter.SelectCommand.Connection.Close();

            return userDataTable;
        }

        /// <summary>
        /// ユーザーのパスワードを取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getPassWord(long userID)
        {
            DataTable tblPass = new DataTable();

            // String query to get password of user by id
            string sql = @"SELECT   [User].[Password]
                            FROM    [User]
                            WHERE   [User].ID = @ID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = userID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblPass);
            conn.Close();

            return tblPass;
        }

        /// <summary>
        /// 利用者の違反数を取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getViolationNumber(long userID)
        {
            DataTable userDataTable = new DataTable();
            SqlDataAdapter userAdapter = new SqlDataAdapter();
            userAdapter.SelectCommand = new SqlCommand();

            userAdapter.SelectCommand.CommandText = @"
                SELECT		[User].ViolationNumber
                FROM		[User]
                WHERE		[User].ID = @userID";

            userAdapter.SelectCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;

            userAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            userAdapter.Fill(userDataTable);
            userAdapter.SelectCommand.Connection.Close();

            return userDataTable;
        }

        /// <summary>
        /// 違反申告を取得 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="violatorID"></param>
        /// <returns></returns>
        public DataTable getBreachReport(long userID, long violatorID)
        {
            DataTable breachDataTable = new DataTable();
            SqlDataAdapter breachAdapter = new SqlDataAdapter();
            breachAdapter.SelectCommand = new SqlCommand();

            breachAdapter.SelectCommand.CommandText = @"
                SELECT		BreachReport.ViolatorID
                FROM		BreachReport
                WHERE		BreachReport.ReporterID = @userID
	                AND		BreachReport.ViolatorID = @violatorID";

            breachAdapter.SelectCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            breachAdapter.SelectCommand.Parameters.Add("@violatorID", SqlDbType.BigInt).Value = violatorID;

            breachAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            breachAdapter.Fill(breachDataTable);
            breachAdapter.SelectCommand.Connection.Close();

            return breachDataTable;
        }

        /// <summary>
        /// 新しいユーザーアカウントを追加する 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public void insertAcc(User user)
        {
            // String query to insert a user
            string sql = @"INSERT INTO [User] (
                                            Name,
                                            Password,
                                            ProfilePicture,
                                            Email,
                                            Phone,
                                            [Address],
                                            ProvinceID,
                                            RegisterDate,
                                            [Status],
                                            ViolationNumber
                                            )
                                    VALUES  (
                                            @name,
                                            @password,
                                            @profilePicture,
                                            @email,
                                            @phone,
                                            @address,
                                            @provinceID,
                                            @date,
                                            @status,
                                            @violationNumber
                                            )";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = user.Name;
            cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = user.Password;
            cmd.Parameters.Add("@profilePicture", SqlDbType.VarChar).Value = user.ProfilePicture;
            cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = user.Email;
            cmd.Parameters.Add("@phone", SqlDbType.Char).Value = user.Phone;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = user.Address;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = user.ProvinceID;
            cmd.Parameters.Add("@date", SqlDbType.Date).Value = user.RegisterDate;
            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = user.Status;
            cmd.Parameters.Add("@violationNumber", SqlDbType.TinyInt).Value = user.ViolationNum;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// 違反申告を追加 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="violatorID"></param>
        /// <param name="reason"></param>
        /// <returns></returns>
        public void insertBreach(long userID, long violatorID, string reason)
        {
            SqlDataAdapter breachAdapter = new SqlDataAdapter();
            breachAdapter.InsertCommand = new SqlCommand();

            breachAdapter.InsertCommand.CommandText = @"
                INSERT INTO BreachReport(
			                ReporterID, 
			                ViolatorID, 
			                Reason)			
                VALUES		(
                            @userID, 
			                @violatorID, 
			                @reason)";

            breachAdapter.InsertCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            breachAdapter.InsertCommand.Parameters.Add("@violatorID", SqlDbType.BigInt).Value = violatorID;
            breachAdapter.InsertCommand.Parameters.Add("@reason", SqlDbType.NVarChar).Value = reason;

            breachAdapter.InsertCommand.Connection = new SqlConnection(conStr);
            breachAdapter.InsertCommand.Connection.Open();
            breachAdapter.InsertCommand.ExecuteNonQuery();
            breachAdapter.InsertCommand.Connection.Close();
        }

        /// <summary>
        /// アクティブ登録済みのアカウント 
        /// </summary>
        /// <param name="email"></param>
        /// <returns></returns>
        public void activeAcc(string email)
        {
            string sql = @"UPDATE   [User]
                            SET     [User].Status = @status
                            WHERE   [User].Email = @email";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@status", SqlDbType.Bit).Value = true;
            cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// ユーザーアカウントの情報を更新 
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public void updateAcc(User user)
        {
            // String query to update user infomation by userID
            string sql = @" UPDATE  [User]
                            SET     [User].Name = @name,
                                    [User].ProfilePicture = @profilePicture,
                                    [User].Email = @email,
                                    [User].Phone = @phone,
                                    [User].ProvinceID = @provinceID,
                                    [User].[Address] = @address
                            WHERE   [User].ID = @ID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = user.Name;
            cmd.Parameters.Add("@profilePicture", SqlDbType.VarChar).Value = user.ProfilePicture;
            cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = user.Email;
            cmd.Parameters.Add("@phone", SqlDbType.Char).Value = user.Phone;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = user.ProvinceID;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = user.Address;
            cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = user.ID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// ユーザーアカウントのパスワードを更新する 
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="newPass"></param>
        /// <returns></returns>
        public void updateAcc(long userID, string newPass)
        {
            // String query to update password by userID
            string sql = @"UPDATE   [User]
                            SET     [User].[PassWord] = @password 
                            WHERE   [User].ID = @ID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = newPass;
            cmd.Parameters.Add("@ID", SqlDbType.BigInt).Value = userID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        /// <summary>
        /// ユーザーアカウントの違反数を更新
        /// 違反数>20は、アカウント場合はロックされます
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="lockAcc"></param>
        /// <returns></returns>
        public void updateAcc(long userID, bool lockAcc)
        {
            SqlDataAdapter userAdapter = new SqlDataAdapter();
            userAdapter.UpdateCommand = new SqlCommand();

            if (lockAcc == true)
            {
                userAdapter.UpdateCommand.CommandText = @"
                    UPDATE		[User]
                    SET			[User].ViolationNumber = ViolationNumber + 1,
			                    [User].[RegisterDate] = @date
                    WHERE		[User].ID = @violatorID";

                userAdapter.UpdateCommand.Parameters.Add("@date", SqlDbType.Date).Value = new DateTime();
            }
            else
            {
                userAdapter.UpdateCommand.CommandText = @"
                    UPDATE		[User]
                    SET			[User].ViolationNumber = ViolationNumber + 1
                    WHERE		[User].ID = @violatorID";
            }

            userAdapter.UpdateCommand.Parameters.Add("@violatorID", SqlDbType.BigInt).Value = userID;

            userAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            userAdapter.UpdateCommand.Connection.Open();
            userAdapter.UpdateCommand.ExecuteNonQuery();
            userAdapter.UpdateCommand.Connection.Close();
        }
    }
}